/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package mapping;
import java.sql.ResultSet;
import entreprise.Patient;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import mapping.SqlLib;
/**
 *
 * @author Quoc-Khai
 */
public class DBPatient {
    public Patient getPatient(String patientID) throws SQLException{
         Patient iPatient = new Patient();
        String selectQuery;
        selectQuery = "SELECT * FROM patient WHERE patientID="+patientID;
        
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);
            
            while(sqlResultSet.next()) {
               iPatient.setPatientID(sqlResultSet.getString("patientID")); 
               iPatient.setPatientName(sqlResultSet.getString("patientName")); 
               iPatient.setPatientSex(sqlResultSet.getString("patientSex")); 
               iPatient.setPatientDateOfBirth(sqlResultSet.getDate("patientDateOfBirth"));
               iPatient.setPatientJob(sqlResultSet.getString("patientJob")); 
               iPatient.setPatientAddress(sqlResultSet.getString("patientAddress")); 
               iPatient.setPatientPhoneNumber(sqlResultSet.getString("patientPhoneNumber")); 
               iPatient.setPatientEmail(sqlResultSet.getString("patientEmail")); 
               iPatient.setPatientHeight(sqlResultSet.getFloat("patientHeight")); 
               iPatient.setPatientWeight(sqlResultSet.getFloat("patientWeight")); 
               iPatient.setPatientBloodType(sqlResultSet.getString("patientBloodType"));  
            }
        } else {
//            System.out.println("Patient not found!");
        } 
        //TODO;
        return iPatient;
    }
    public boolean createPatient(Patient patient){
        //TODO;
        java.sql.Date sqlDate = new java.sql.Date(patient.getPatientDateOfBirth().getTime());
        String insertQuery;
        insertQuery = "INSERT INTO patient (patientName, patientSex, patientDateOfBirth, patientJob, patientAddress, patientPhoneNumber, patientEmail, patientHeight, patientWeight, patientBloodType)"
                + "VALUES "
                + "('" + 
                patient.getPatientName() + "','" + 
                patient.getPatientSex()+ "','" + 
                sqlDate + "','" + 
                patient.getPatientJob()+ "','" + 
                patient.getPatientAddress()+ "','" + 
                patient.getPatientPhoneNumber()+ "','" + 
                patient.getPatientEmail()+ "','" + 
                patient.getPatientHeight()+ "','" + 
                patient.getPatientWeight()+ "','" + 
                patient.getPatientBloodType() + 
                "')";
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(insertQuery);
//            System.out.println("Insert Patient success!");
            return true;
        } 
        return false;
    }
    public boolean updatePatient(Patient patient){
        //TODO
        java.sql.Date sqlDate = new java.sql.Date(patient.getPatientDateOfBirth().getTime());
        String updateQuery;
        updateQuery = "UPDATE patient SET "
                + "patientName='" + patient.getPatientName()
                + "', patientSex='" + patient.getPatientSex()
                + "',patientDateOfBirth='" + sqlDate
                + "', patientJob='" + patient.getPatientJob()
                + "', patientAddress='" + patient.getPatientAddress()
                + "', patientPhoneNumber='" + patient.getPatientPhoneNumber()
                + "', patientEmail='" + patient.getPatientEmail()
                + "', patientHeight='" + patient.getPatientHeight()
                + "', patientWeight='" + patient.getPatientWeight()
                + "', patientBloodType='" + patient.getPatientBloodType()
                + "' WHERE patientID="+patient.getPatientID();
        
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(updateQuery);
//            System.out.println("Update Patient success!");
            return true;
        }
        return false;
    }
    public boolean deletePatient(String patientID){
        //TODO
        String deleteQuery;
        deleteQuery = "DELETE FROM patient WHERE patientID="+patientID; 
        SqlLib connec = new SqlLib();
        
        if (connec.initConnection()) {
            connec.execQuery(deleteQuery);
//            System.out.println("Patient Drug success!");
            return true;
        } 
        return false;
    }    
    public ArrayList<Patient> getPatients(String patientName,int from, int numberOfRow){
        ArrayList<Patient> patients=new ArrayList<>();
        String selectQuery="";
        if(patientName.equals("")){
            selectQuery="select * from patient LIMIT "+from+","+numberOfRow;
        }else{
            selectQuery="select * from patient where patientName LIKE '%"+patientName+"%' LIMIT "+from+","+numberOfRow;
        }        
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                while(sqlResultSet.next()) {
                    Patient iPatient=new Patient();
                    iPatient.setPatientID(sqlResultSet.getString("patientID")); 
                    iPatient.setPatientName(sqlResultSet.getString("patientName")); 
                    iPatient.setPatientSex(sqlResultSet.getString("patientSex")); 
                    iPatient.setPatientDateOfBirth(sqlResultSet.getDate("patientDateOfBirth"));
                    iPatient.setPatientJob(sqlResultSet.getString("patientJob")); 
                    iPatient.setPatientAddress(sqlResultSet.getString("patientAddress")); 
                    iPatient.setPatientPhoneNumber(sqlResultSet.getString("patientPhoneNumber")); 
                    iPatient.setPatientEmail(sqlResultSet.getString("patientEmail")); 
                    iPatient.setPatientHeight(sqlResultSet.getFloat("patientHeight")); 
                    iPatient.setPatientWeight(sqlResultSet.getFloat("patientWeight")); 
                    iPatient.setPatientBloodType(sqlResultSet.getString("patientBloodType"));
                    patients.add(iPatient);
                }
            } catch (SQLException ex) {
                Logger.getLogger(DBPatient.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
//            System.out.println("Drug not found!");
        }         
        return patients;
    }
    public int getNumberOfPatients(String patientName){    
        String selectQuery="";
        if(patientName.equals("")){
            selectQuery="select count(*) as RECORDCOUNT from patient";
        }else{
            selectQuery="select count(*) as RECORDCOUNT from patient where patientName LIKE '%"+patientName+"%'";
        }
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                connec.closeConnection();
                int numberOfRow=0;
                while(sqlResultSet.next()) {
                    numberOfRow=sqlResultSet.getInt("RECORDCOUNT");
                }
                return numberOfRow;
            } catch (SQLException ex) {
                Logger.getLogger(DBPatient.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
//            System.out.println("Drug not found!");
            return 0;
        } 
        return 0;
    }
    public int getLatestPatientID() {
        String selectQuery= "SELECT * FROM patient order by sessionID desc limit 1";
        SqlLib connec = new SqlLib();
        if (connec.initConnection()) {
            ResultSet sqlResultSet = connec.getDataFromQuery(selectQuery);            
            try {
                while(sqlResultSet.next()) {
                    return sqlResultSet.getInt("patientID");
                }                
            } catch (SQLException ex) {
                Logger.getLogger(DBSession.class.getName()).log(Level.SEVERE, null, ex);
                return 0;
            }
            connec.closeConnection();
        } else {
//            System.out.println("Session not found!");
              return 0;
        }
        return 0;
    }    
}
